﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Catering_DBOpertion;//Please add references
using System.Linq;

namespace Catering_Operation
{
    /// <summary>
    /// 数据访问类:Dynamic
    /// </summary>
    public partial class Dynamic
    {
        public Dynamic()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "Dynamic");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Dynamic");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Catering_Model.Dynamic model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Dynamic(");
            strSql.Append("id,GroupID,ClassID,Date,Number,Catering,Charge,HPersons,QC,persons,Eat,EatTID,temp1,temp2)");
            strSql.Append(" values (");
            strSql.Append("@id,@GroupID,@ClassID,@Date,@Number,@Catering,@Charge,@HPersons,@QC,@persons,@Eat,@EatTID,@temp1,@temp2)");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime),
					new SqlParameter("@Number", SqlDbType.Int,4),
					new SqlParameter("@Catering", SqlDbType.Int,4),
					new SqlParameter("@Charge", SqlDbType.Int,4),
					new SqlParameter("@HPersons", SqlDbType.Int,4),
					new SqlParameter("@QC", SqlDbType.Int,4),
					new SqlParameter("@persons", SqlDbType.Int,4),
					new SqlParameter("@Eat", SqlDbType.Int,4),
					new SqlParameter("@EatTID", SqlDbType.Int,4),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200)};
            parameters[0].Value = model.id;
            parameters[1].Value = model.GroupID;
            parameters[2].Value = model.ClassID;
            parameters[3].Value = model.Date;
            parameters[4].Value = model.Number;
            parameters[5].Value = model.Catering;
            parameters[6].Value = model.Charge;
            parameters[7].Value = model.HPersons;
            parameters[8].Value = model.QC;
            parameters[9].Value = model.persons;
            parameters[10].Value = model.Eat;
            parameters[11].Value = model.EatTID;
            parameters[12].Value = model.temp1;
            parameters[13].Value = model.temp2;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Catering_Model.Dynamic model, bool Isxiugai)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Dynamic(");
            strSql.Append("GroupID,ClassID,Date,Number,Catering,Charge,HPersons,QC,persons,Eat,EatTID,temp1,temp2)");
            strSql.Append(" values (");
            strSql.Append("@GroupID,@ClassID,@Date,@Number,@Catering,@Charge,@HPersons,@QC,@persons,@Eat,@EatTID,@temp1,@temp2)");
            SqlParameter[] parameters = {
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime),
					new SqlParameter("@Number", SqlDbType.Int,4),
					new SqlParameter("@Catering", SqlDbType.Int,4),
					new SqlParameter("@Charge", SqlDbType.Int,4),
					new SqlParameter("@HPersons", SqlDbType.Int,4),
					new SqlParameter("@QC", SqlDbType.Int,4),
					new SqlParameter("@persons", SqlDbType.Int,4),
					new SqlParameter("@Eat", SqlDbType.Int,4),
					new SqlParameter("@EatTID", SqlDbType.Int,4),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200)};
            parameters[0].Value = model.GroupID;
            parameters[1].Value = model.ClassID;
            parameters[2].Value = model.Date;
            parameters[3].Value = model.Number;
            parameters[4].Value = model.Catering;
            parameters[5].Value = model.Charge;
            parameters[6].Value = model.HPersons;
            parameters[7].Value = model.QC;
            parameters[8].Value = model.persons;
            parameters[9].Value = model.Eat;
            parameters[10].Value = model.EatTID;
            parameters[11].Value = model.temp1;
            parameters[12].Value = model.temp2;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Catering_Model.Dynamic model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Dynamic set ");
            strSql.Append("GroupID=@GroupID,");
            strSql.Append("ClassID=@ClassID,");
            strSql.Append("Date=@Date,");
            strSql.Append("Number=@Number,");
            strSql.Append("Catering=@Catering,");
            strSql.Append("Charge=@Charge,");
            strSql.Append("HPersons=@HPersons,");
            strSql.Append("QC=@QC,");
            strSql.Append("persons=@persons,");
            strSql.Append("Eat=@Eat,");
            strSql.Append("EatTID=@EatTID,");
            strSql.Append("temp1=@temp1,");
            strSql.Append("temp2=@temp2");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@GroupID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@Date", SqlDbType.DateTime),
					new SqlParameter("@Number", SqlDbType.Int,4),
					new SqlParameter("@Catering", SqlDbType.Int,4),
					new SqlParameter("@Charge", SqlDbType.Int,4),
					new SqlParameter("@HPersons", SqlDbType.Int,4),
					new SqlParameter("@QC", SqlDbType.Int,4),
					new SqlParameter("@persons", SqlDbType.Int,4),
					new SqlParameter("@Eat", SqlDbType.Int,4),
					new SqlParameter("@EatTID", SqlDbType.Int,4),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.GroupID;
            parameters[1].Value = model.ClassID;
            parameters[2].Value = model.Date;
            parameters[3].Value = model.Number;
            parameters[4].Value = model.Catering;
            parameters[5].Value = model.Charge;
            parameters[6].Value = model.HPersons;
            parameters[7].Value = model.QC;
            parameters[8].Value = model.persons;
            parameters[9].Value = model.Eat;
            parameters[10].Value = model.EatTID;
            parameters[11].Value = model.temp1;
            parameters[12].Value = model.temp2;
            parameters[13].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Dynamic ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Dynamic ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteListBywhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Dynamic ");
            strSql.Append(" where " + where);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Catering_Model.Dynamic GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,GroupID,ClassID,Date,Number,Catering,Charge,HPersons,QC,persons,Eat,EatTID,temp1,temp2 from Dynamic ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Catering_Model.Dynamic model = new Catering_Model.Dynamic();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["GroupID"] != null && ds.Tables[0].Rows[0]["GroupID"].ToString() != "")
                {
                    model.GroupID = int.Parse(ds.Tables[0].Rows[0]["GroupID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ClassID"] != null && ds.Tables[0].Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = int.Parse(ds.Tables[0].Rows[0]["ClassID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Date"] != null && ds.Tables[0].Rows[0]["Date"].ToString() != "")
                {
                    model.Date = DateTime.Parse(ds.Tables[0].Rows[0]["Date"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Number"] != null && ds.Tables[0].Rows[0]["Number"].ToString() != "")
                {
                    model.Number = int.Parse(ds.Tables[0].Rows[0]["Number"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Catering"] != null && ds.Tables[0].Rows[0]["Catering"].ToString() != "")
                {
                    model.Catering = int.Parse(ds.Tables[0].Rows[0]["Catering"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Charge"] != null && ds.Tables[0].Rows[0]["Charge"].ToString() != "")
                {
                    model.Charge = int.Parse(ds.Tables[0].Rows[0]["Charge"].ToString());
                }
                if (ds.Tables[0].Rows[0]["HPersons"] != null && ds.Tables[0].Rows[0]["HPersons"].ToString() != "")
                {
                    model.HPersons = int.Parse(ds.Tables[0].Rows[0]["HPersons"].ToString());
                }
                if (ds.Tables[0].Rows[0]["QC"] != null && ds.Tables[0].Rows[0]["QC"].ToString() != "")
                {
                    model.QC = int.Parse(ds.Tables[0].Rows[0]["QC"].ToString());
                }
                if (ds.Tables[0].Rows[0]["persons"] != null && ds.Tables[0].Rows[0]["persons"].ToString() != "")
                {
                    model.persons = int.Parse(ds.Tables[0].Rows[0]["persons"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Eat"] != null && ds.Tables[0].Rows[0]["Eat"].ToString() != "")
                {
                    model.Eat = int.Parse(ds.Tables[0].Rows[0]["Eat"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EatTID"] != null && ds.Tables[0].Rows[0]["EatTID"].ToString() != "")
                {
                    model.EatTID = int.Parse(ds.Tables[0].Rows[0]["EatTID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["temp1"] != null && ds.Tables[0].Rows[0]["temp1"].ToString() != "")
                {
                    model.temp1 = ds.Tables[0].Rows[0]["temp1"].ToString();
                }
                if (ds.Tables[0].Rows[0]["temp2"] != null && ds.Tables[0].Rows[0]["temp2"].ToString() != "")
                {
                    model.temp2 = ds.Tables[0].Rows[0]["temp2"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,GroupID,ClassID,Date,Number,Catering,Charge,HPersons,QC,persons,Eat,EatTID,temp1,temp2 ");
            strSql.Append(" FROM Dynamic ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,GroupID,ClassID,Date,Number,Catering,Charge,HPersons,QC,persons,Eat,EatTID,temp1,temp2 ");
            strSql.Append(" FROM Dynamic ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Dynamic";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/


        /// <summary>
        /// 全年各班组就餐人数
        /// </summary>
        /// <param name="year">时间</param>
        /// <param name="classId">班组id，可以传入null</param>
        /// <returns></returns>
        public DataTable GetEat_Of_YearByTime(string strarealist, string[] year, string groupid, string classId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select yearofdate,sum(qc+persons+Catering) AS Number,groupid,classid,CName from View_Dynamic where 1=1");
            if (year.Count() == 1)
            {
                strSql.Append(" and yearofdate = " + year[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < year.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append("yearofdate = " + year[i]);
                    }
                    else
                    {
                        strSql.Append("or yearofdate = " + year[i]);
                    }
                }
                strSql.Append(")");
            }
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                strSql.Append(" and GroupID=" + groupid);
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and ClassID=" + classId);
            }
            strSql.Append(" group by yearofdate,groupid,classid,CName ");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        public DataTable GetEatNumbersByYear(string strclasslist, string year, string classsId)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            if (!string.IsNullOrEmpty(classsId) && !classsId.Equals("-10"))
            {
                sql.Append(@"SELECT  * from Eat_Of_Year where ClassID= " + classsId);
                if (!string.IsNullOrEmpty(year))
                {
                    sql.Append(" and date='" + year + "'");
                }
            }
            else
            {
                sql.Append(@"SELECT SUM(CONVERT(int, dbo.Dynamic.Number)) AS number,");
                sql.Append("YEAR(dbo.Dynamic.Date) AS date, dbo.getChNum(MONTH(dbo.Dynamic.Date)) AS chmonth,");
                sql.Append("MONTH(dbo.Dynamic.Date) AS nummonth FROM dbo.ClassInfo INNER JOIN ");
                sql.Append(" dbo.Dynamic ON dbo.ClassInfo.id = dbo.Dynamic.ClassID INNER JOIN ");
                sql.Append(" dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id ");
                sql.Append(" WHERE dbo.ClassInfo.ID IN (" + strclasslist + ") ");
                if (!string.IsNullOrEmpty(year))
                {
                    sql.Append(" and YEAR(dbo.Dynamic.Date)='" + year + "'");
                }
                sql.Append(" GROUP BY YEAR(dbo.Dynamic.Date), dbo.getChNum(MONTH(dbo.Dynamic.Date)),");
                sql.Append(" MONTH(dbo.Dynamic.Date)");
            }

            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        public decimal GetEatNumberByMonth(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT SUM(Number) AS Number FROM   View_Dynamic where 1=1 ");
            sql.Append(where);
            DataTable dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            decimal eat = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                decimal.TryParse(dt.Rows[0]["Number"].ToString(), out eat);
            }
            return eat;
        }
        #endregion  Method

        /// <summary>
        /// 各班组每月的总人数
        /// </summary>
        /// <returns></returns>
        public DataTable GetEatNumbersByYear(string whereclase)
        {
            string sql = "select classid,groupid,yearofdate,monthofdate,sum(QC+Persons) as dnumber,"
            + "cname from view_Dynamic where 1=1 " + whereclase + " group by groupid,classid,yearofdate,monthofdate,cname";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 各班组每年的总人数
        /// </summary>
        /// <returns></returns>
        public DataTable GetEatNumbersCount(string whereclase)
        {
            string sql = "select sum(QC+Persons) as dnumber from view_Dynamic where 1=1 " + whereclase;
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            return dt;
        }
        public DataTable GetCateringAndEatNumber(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select sum(catering) as Catering,sum(QC+persons) as EatNumber from dynamic where 1=1 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 按班组和月份获取人数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetEatNumberByClassIDAndMonth(string where)
        {
            StringBuilder sql = new StringBuilder("SELECT SUM(QC) AS QC,SUM(Persons) AS Persons,SUM(Catering) AS Catering From Dynamic WHERE 1=1 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
    }
}

